﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class BookingTicketDAL
    {
        private const string SQL_SELECT_WS_BOOKING_TICKET = "select BKT_ID as PID, BKT_BOOKING_ID as BOOKING_ID,BKT_TICKET_NO as TICKET_NO,BKT_AIRLINE_ID as AIRLINE_ID,BKT_FLIGHT_NO as FLIGHT_NO,date_format(BKT_DEPARTURE_TIME,'%Y-%m-%d %H:%i:%s') as DEPARTURE_TIME,BKT_DEPARTURE_CITY_ID as DEPARTURE_CITY_ID,BKT_ARRIVAL_CITY_ID as ARRIVAL_CITY_ID,BKT_TICKET_PRICE as TICKET_PRICE,BKT_REFUND_FLG as REFUND_FLG,BKT_RCRE_STAFF_ID as RCRE_STAFF_ID,BKT_RCRE_TIME as RCRE_TIME,BKT_LCHG_STAFF_ID as LCHG_STAFF_ID,BKT_LCHG_TIME as LCHG_TIME,(select ALI_AIRLINE_NAME from WS_AIRLINE where ALI_AIRLINE_ID=BKT_AIRLINE_ID) as AIRLINE_NAME,(select concat(CIT_CITY_NAME,'(',CIT_AIRPORT_NAME,')') from WS_CITY where CIT_CITY_ID=BKT_DEPARTURE_CITY_ID) as DEPARTURE_CITY_NAME,(select concat(CIT_CITY_NAME,'(',CIT_AIRPORT_NAME,')') from WS_CITY where CIT_CITY_ID=BKT_ARRIVAL_CITY_ID) as ARRIVAL_CITY_NAME from WS_BOOKING_TICKET where 1=1 ";
        private const string SQL_INSERT_WS_BOOKING_TICKET = "insert into WS_BOOKING_TICKET(BKT_ID,BKT_BOOKING_ID,BKT_TICKET_NO,BKT_AIRLINE_ID,BKT_FLIGHT_NO,BKT_DEPARTURE_TIME,BKT_DEPARTURE_CITY_ID,BKT_ARRIVAL_CITY_ID,BKT_TICKET_PRICE,BKT_REFUND_FLG,BKT_RCRE_STAFF_ID,BKT_RCRE_TIME) values(@PId,@BookingId,@TicketNo,@AirlineId,@FlightNo,@DepartureTime,@DepartureCityId,@ArrivalCityId,@TicketPrice,@RefundFlag,@LastStaffId,@LastTime)";
        private const string SQL_DELETE_WS_BOOKING_TICKET = "delete from WS_BOOKING_TICKET where 1=1 ";
        private const string SQL_COUNT_WS_BOOKING_TICKET = "select count(BKT_BOOKING_ID) from WS_BOOKING_TICKET where 1=1";

        private const string SQL_WHERE_ID = " and BKT_ID=@PId ";
        private const string SQL_WHERE_BOOKING_ID = " and BKT_BOOKING_ID=@BookingId ";
        private const string SQL_WHERE_AIRLINE_ID = " and BKT_AIRLINE_ID=@AirlineId ";
        private const string SQL_WHERE_DEPARTURE_CITY_ID = " and BKT_DEPARTURE_CITY_ID=@DepartureCityId ";
        private const string SQL_WHERE_ARRIVAL_CITY_ID = " and BKT_ARRIVAL_CITY_ID=@ArrivalCityId ";

        private const string PARM_ID = "@PId";
        private const string PARM_BOOKING_ID = "@BookingId";
        private const string PARM_TICKET_NO = "@TicketNo";
        private const string PARM_AIRLINE_ID = "@AirlineId";
        private const string PARM_FLIGHT_NO = "@FlightNo";
        private const string PARM_DEPARTURE_TIME = "@DepartureTime";
        private const string PARM_DEPARTURE_CITY_ID = "@DepartureCityId";
        private const string PARM_ARRIVAL_CITY_ID = "@ArrivalCityId";
        private const string PARM_TICKET_PRICE = "@TicketPrice";
        private const string PARM_REFUND_FLG = "@RefundFlag";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";

        Nullable<DateTime> NullDateTime = null;

        private static BookingTicketDAL instance = new BookingTicketDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static BookingTicketDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(string bookingId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_BOOKING_TICKET);
            sbSQL.Append(SQL_WHERE_BOOKING_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = bookingId });

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public List<BookingTicketModel> Gets(string bookingId)
        {
            using (DataSet ds = this.GetDataSet(bookingId))
            {
                DataTable dt = ds.Tables[0];
                List<BookingTicketModel> list = dt.AsEnumerable().Select(t => new BookingTicketModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    BookingId = Convert.ToString(t.ItemArray[1]),
                    TicketNo = Convert.ToString(t.ItemArray[2]),
                    AirlineId = Convert.ToString(t.ItemArray[3]),
                    FlightNo = Convert.ToString(t.ItemArray[4]),
                    DepartureTime = Convert.ToDateTime(t.ItemArray[5]),
                    DepartureCityId = Convert.ToString(t.ItemArray[6]),
                    ArrivalCityId = Convert.ToString(t.ItemArray[7]),
                    TicketPrice = Convert.ToDecimal(t.ItemArray[8]),
                    Refund = string.Equals(Convert.ToString(t.ItemArray[9]),"Y"),
                    CreatedStaffId = Convert.ToString(t.ItemArray[10]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[11]),
                    LastModifiedStaffId = t.IsNull(12) ? string.Empty : Convert.ToString(t.ItemArray[12]),
                    LastModifiedDate = t.IsNull(13) ? NullDateTime : Convert.ToDateTime(t.ItemArray[13])
                }).ToList<BookingTicketModel>();

                return list;
            }
        }
        public int Insert(DbTransaction trans, BookingTicketModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.BookingId });
            parms.Add(new MySqlParameter(PARM_TICKET_NO, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.TicketNo });
            parms.Add(new MySqlParameter(PARM_AIRLINE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.AirlineId });
            parms.Add(new MySqlParameter(PARM_FLIGHT_NO, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.FlightNo });
            parms.Add(new MySqlParameter(PARM_DEPARTURE_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.DepartureTime });
            parms.Add(new MySqlParameter(PARM_DEPARTURE_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.DepartureCityId });
            parms.Add(new MySqlParameter(PARM_ARRIVAL_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ArrivalCityId });
            parms.Add(new MySqlParameter(PARM_TICKET_PRICE, MySqlDbType.Decimal) { Direction = ParameterDirection.Input, Value = model.TicketPrice });
            parms.Add(new MySqlParameter(PARM_REFUND_FLG, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Refund ? "Y" : "N" });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_BOOKING_TICKET, parms.ToArray());
        }
        public int Delete(DbTransaction trans, string bookingId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_BOOKING_TICKET);
            sbSQL.Append(SQL_WHERE_BOOKING_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = bookingId });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool AirlineUsing(string airlineId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_BOOKING_TICKET);
            sbSQL.Append(SQL_WHERE_AIRLINE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_AIRLINE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = airlineId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool DepartureCityUsing(string departureCityId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_BOOKING_TICKET);
            sbSQL.Append(SQL_WHERE_DEPARTURE_CITY_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_DEPARTURE_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = departureCityId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool ArrivalCityUsing(string arrivalCityId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_BOOKING_TICKET);
            sbSQL.Append(SQL_WHERE_ARRIVAL_CITY_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ARRIVAL_CITY_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = arrivalCityId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
